﻿Imports System.Data.OleDb
Public Class frmThemGiaSanPhamTheoKhachHang
    Public GiaId As Integer = -1
#Region "Form Load"
    Private aMaSanPham As New AutoCompleteStringCollection
    Private Sub frmThemGiaSanPhamTheoKhachHang_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            Me.Height = 200
            Dim newPoint As New System.Drawing.Point
            newPoint.X = 461
            newPoint.Y = 132
            PanelButton.Location = newPoint
            Me.CenterToParent()
            txtDonGiaI.Text = "0.00"
            txtDonGiaII.Text = "0.00"
            txtKhachHang.Text = ""
            txtTenSanPham.Text = ""
            'Auto Complete
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtProductDetail As New DataTable
            Dim query As String = "Select MaSanPham From SanPham Where Xoa=0"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtProductDetail)
            aMaSanPham = New AutoCompleteStringCollection
            For i As Integer = 0 To dtProductDetail.Rows.Count - 1
                aMaSanPham.Add(dtProductDetail.Rows(i)("MaSanPham"))
            Next
            txtTenSanPham.AutoCompleteMode = AutoCompleteMode.SuggestAppend
            txtTenSanPham.AutoCompleteSource = AutoCompleteSource.CustomSource
            txtTenSanPham.AutoCompleteCustomSource = aMaSanPham
            'End Auto
            If GiaId <> -1 Then
                'Dim dbConn As New OleDb.OleDbConnection
                'Dim dataAccess As New DataAccess
                Dim dtSanPhamNhieuGia As New DataTable
                query = "Select SPM.GiaId,SP.IDSanPham,SP.MaSanPham,SP.TenSanPham,SP.DonGia,SPM.DonGiaII,KH.KhachHangID,KH.TenKhachHang,KH.DiaChi From SanPhamNhieuGia As SPM" & _
                                        " Left Outer Join SanPham As SP On SP.IDSanPham=SPM.IDSanPham" & _
                                        " Left Outer Join DanhMucKhachHang As KH On KH.KhachHangID = SPM.IDKhachHang" & _
                                        " Where SPM.Xoa=0 And SPM.GiaID=" & GiaId
                'Dim adapter As OleDb.OleDbDataAdapter
                'dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtSanPhamNhieuGia)
                dbConn.Close()
                txtTenSanPham.Text = dtSanPhamNhieuGia.Rows(0)("MaSanPham")
                lblTenSanPham.Text = dtSanPhamNhieuGia.Rows(0)("TenSanPham")
                txtDonGiaI.Text = dtSanPhamNhieuGia.Rows(0)("DonGia")
                txtDonGiaII.Text = dtSanPhamNhieuGia.Rows(0)("DonGiaII")

                Label1.Text = dtSanPhamNhieuGia.Rows(0)("KhachHangID")
                txtKhachHang.Text = dtSanPhamNhieuGia.Rows(0)("TenKhachHang")

                Panel_KhachHang.Visible = False
                PanelSanPham_Search.Visible = False
            End If
            dbConn.Close()
        Catch ex As Exception

        End Try
    End Sub
#End Region
#Region "Lưu"
    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        Try
            Dim dataAccess As New DataAccess
            Dim dbConn As New OleDb.OleDbConnection
            Dim dbCommand As OleDbCommand
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim query As String = ""
            Dim IDSanPham As Integer
            Dim IDKhachHang As String = Label1.Text
            IDSanPham = KiemTraTonTaiSanPham(txtTenSanPham.Text)
            'Kiểm Tra Sản Phẩm
            If IDSanPham = 0 Then
                MessageBox.Show("Tên sản phẩm bạn chọn chưa có trong danh mục.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                txtTenSanPham.Focus()
                Exit Sub
            End If
            If IsNotExist() Then
                MessageBox.Show("Tên khách hàng bạn chọn chưa có trong danh mục.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                txtKhachHang.Focus()
                Exit Sub
            End If
            If txtDonGiaII.Text = "" Then
                MessageBox.Show("Bạn chưa nhập đơn giá.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                txtDonGiaII.Focus()
                Exit Sub
            End If
            If GiaId = -1 Then
                'check nếu giá sản phầm này đã có cho khác hàng này rồi thì thông báo
                query = "Select Count(*) From SanPhamNhieuGia Where Xoa=0 And IDSanPham=" & IDSanPham & " And IDKhachHang=" & IDKhachHang
                dbCommand = New OleDb.OleDbCommand(query, dbConn)
                Dim iResult As Integer
                iResult = dbCommand.ExecuteScalar()
                If iResult > 0 Then
                    MessageBox.Show("Giá của sản phẩm này với khách hàng đã có trong danh mục.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    dbConn.Close()
                    Exit Sub
                End If
                query = String.Format("Insert Into SanPhamNhieuGia(IDSanPham,IDKhachHang,DonGiaII,NgayTao,NguoiTao) Values({0},{1},{2},N'{3}',N'{4}')", IDSanPham, IDKhachHang, Single.Parse(txtDonGiaII.Text, Globalization.NumberStyles.Currency), NgayTao, NguoiTao)
            Else
                query = String.Format("Update SanPhamNhieuGia Set IDSanPham={0},IDKhachHang={1},DonGiaII={2},NgaySuaGanNhat=N'{3}',NguoiSuaGanNhat=N'{4}' Where GiaId={5}", IDSanPham, IDKhachHang, Single.Parse(txtDonGiaII.Text, Globalization.NumberStyles.Currency), NgayTao, NguoiTao, GiaId)
            End If
            dbCommand = New OleDbCommand(query, dbConn)
            dbCommand.ExecuteNonQuery()
            dbConn.Close()
            txtKhachHang.Text = ""
            Label1.TabIndex = "0"
            If GiaId <> -1 Then
                GiaId = -1
                Me.Close()
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click
        Try
            txtKhachHang.Text = ""
            Label1.TabIndex = "0"
            GiaId = -1
            Me.Close()
        Catch ex As Exception

        End Try
    End Sub
#End Region
#Region "Resize Form"
    Private Sub Panel_KhachHang_VisibleChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Panel_KhachHang.VisibleChanged
        Try
            If Panel_KhachHang.Visible Then
                Me.Height = 465
                Dim newPoint As New System.Drawing.Point
                newPoint.X = 461
                newPoint.Y = 388
                PanelButton.Location = newPoint
            Else
                Me.Height = 200
                Dim newPoint As New System.Drawing.Point
                newPoint.X = 461
                newPoint.Y = 132
                PanelButton.Location = newPoint
            End If
        Catch ex As Exception

        End Try
    End Sub
    Private Sub PanelSanPham_Search_VisibleChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles PanelSanPham_Search.VisibleChanged
        Try
            If PanelSanPham_Search.Visible Then
                Me.Height = 346
                Dim newPoint As New System.Drawing.Point
                newPoint.X = 461
                newPoint.Y = 266
                PanelButton.Location = newPoint
            Else
                Me.Height = 200
                Dim newPoint As New System.Drawing.Point
                newPoint.X = 461
                newPoint.Y = 132
                PanelButton.Location = newPoint
            End If
        Catch ex As Exception

        End Try
    End Sub
#End Region
#Region "Thêm Khách Hàng"
    Sub KhachHang_Quick_Search()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsKhachHang As New DataSet
            Dim dtKhachHang As New DataTable
            Dim query As String = "Select KhachHangId,MaKhachHang,TenKhachHang,SoDienThoai,DiaChi From DanhMucKhachHang Where TenKhachHang Like N'%" & txtKhachHang.Text.Trim & "%'"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsKhachHang)
            dbConn.Close()
            dtKhachHang = dsKhachHang.Tables(0)
            Panel_KhachHang.Visible = True
            dgv_KhachHang_QuickSearch.AutoGenerateColumns = False
            dgv_KhachHang_QuickSearch.DataSource = dtKhachHang
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Private Sub dgv_KhachHang_QuickSearch_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv_KhachHang_QuickSearch.CellDoubleClick
        Try
            'Gán ID Khách Hàng
            Label1.Text = dgv_KhachHang_QuickSearch.Rows(dgv_KhachHang_QuickSearch.CurrentCell.RowIndex).Cells("KhachHangId_Search").Value.ToString()
            '---------Lấy Thống Tin Khách Hàng----------
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsTinhThanh As New DataSet
            Dim dtTinhThanh As New DataTable
            Dim query As String = "Select * From DanhMucKhachHang Where KhachHangId=" & Label1.Text
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsTinhThanh)
            dbConn.Close()
            dtTinhThanh = dsTinhThanh.Tables(0)
            '
            txtKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("TenKhachHang")), "", dtTinhThanh.Rows(0)("TenKhachHang"))
            '
            Panel_KhachHang.Visible = False
            cmdSave.Focus()
        Catch ex As Exception

        End Try
    End Sub
    Private Sub txtKhachHang_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtKhachHang.KeyUp
        Try
            If txtKhachHang.Text <> "" Then
                KhachHang_Quick_Search()
            Else
                Panel_KhachHang.Visible = False
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub

    Function IsNotExist() As Boolean
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsKhachHang As New DataSet
            Dim dtKhachHang As New DataTable
            Dim query As String = "Select Count(*) From DanhMucKhachHang Where LOWER(TenKhachHang)=N'" & txtKhachHang.Text.ToLower.Trim & "'"
            Dim dbCommand As OleDb.OleDbCommand
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            dbCommand = New OleDb.OleDbCommand(query, dbConn)
            Dim iResult As Integer
            iResult = dbCommand.ExecuteScalar()
            If iResult = 0 Then
                Return True
            Else
                Return False
            End If
            dbConn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Function
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Panel_KhachHang.Visible = False
        Catch ex As Exception

        End Try
    End Sub
#End Region
#Region "Thêm Sản Phẩm"
    Private Function KiemTraTonTaiSanPham(ByVal TenSanPham As String) As Integer
        Try
            Dim dataAccess As New DataAccess
            Dim dbConn As New OleDb.OleDbConnection
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim dbCommand As OleDbCommand
            Dim query As String = "Select IDSanPham From SanPham Where MaSanPham=N'" & TenSanPham & "'"
            dbCommand = New OleDbCommand(query, dbConn)
            If IsDBNull(dbCommand.ExecuteScalar) Then
                Return 0
            Else
                Return dbCommand.ExecuteScalar()
            End If
            dbConn.Close()
        Catch ex As Exception
            ShowError()
        End Try
    End Function
    Private Sub SanPham_Search(ByVal KeyWord As String)
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsSanPham As New DataSet
            Dim dtSanPham As New DataTable
            Dim query As String = "Select IDSanPham,MaLoai,TenLoai,MaSanPham,TenSanPham,TenDonViTinh,DonViTinh.TenDonVi,SanPham.GhiChu,SanPham.Xoa,SanPham.DonGia From SanPham " & _
                                    " Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai " & _
                                    " Left Outer Join DVTSanPham On SanPham.DVTSanPhamId=DVTSanPham.DVTSanPhamId" & _
                                    " Left Outer Join DonViTinh On DonViTinh.IdDonVi=DVTSanPham.IDDonViTinh" & _
                                    " Where SanPham.Xoa=0 And SanPham.MaSanPham Like N'%" & KeyWord & "%' Or SanPham.TenSanPham Like N'%" & KeyWord & "%'  Order By TenSanPham"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsSanPham)
            dbConn.Close()
            dgvSanPham_Search.AutoGenerateColumns = False
            dtSanPham = dsSanPham.Tables(0)
            dtSanPham.Columns.Add("STT")
            For i As Integer = 0 To dtSanPham.Rows.Count - 1
                dtSanPham.Rows(i)("STT") = i + 1
                'chú giải
            Next
            dgvSanPham_Search.DataSource = dtSanPham
            If dgvSanPham_Search.Rows.Count - 1 > 0 Then
                dgvSanPham_Search.Rows(0).Selected = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub txtTenSanPham_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyDown
        Try
            If e.KeyCode = Keys.Enter Then
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dtSanPham As New DataTable
                Dim query As String = "Select DonGia From SanPham Where Xoa=0 And MaSanPham='" & txtTenSanPham.Text.Trim & "'"
                Dim adapter As OleDb.OleDbDataAdapter
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtSanPham)
                dbConn.Close()
                If dtSanPham.Rows.Count > 0 Then
                    txtDonGiaI.Value = dtSanPham.Rows(0)("DonGia")
                Else
                    txtDonGiaI.Value = "0.00"
                End If
            End If
        Catch ex As Exception

        End Try
    End Sub

    Private Sub txtTenSanPham_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyUp
        Try
            'If txtTenSanPham.Text <> "" Then
            '    SanPham_Search(txtTenSanPham.Text)
            '    PanelSanPham_Search.Visible = True
            'Else
            '    'hide search panel
            '    PanelSanPham_Search.Visible = False
            'End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub dgvSanPham_Search_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSanPham_Search.CellDoubleClick
        Try
            'Gan Du Lieu
            txtDonGiaI.Text = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("DonGia").Value.ToString
            txtTenSanPham.Text = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
            lblTenSanPham.Text = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenSanPham").Value.ToString
            'hide search panel
            PanelSanPham_Search.Visible = False
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub cmdDong_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDong.Click
        Try
            PanelSanPham_Search.Visible = False
        Catch ex As Exception

        End Try
    End Sub
#End Region







    'Private Sub cmdAddNew_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdAddNew.Enter
    '    Try
    '        Panel_KhachHang.Visible = False
    '        'kiem tra nếu tên khách hàng chua có trong list thi thong bao
    '        If IsNotExist() Then
    '            MessageBox.Show("Tên khách hàng bạn nhập chưa có trong danh mục.Vui lòng kiểm tra lại", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
    '            txtKhachHang.Focus()
    '        End If
    '    Catch ex As Exception

    '    End Try
    'End Sub

    Private Sub txtDonGiaI_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtDonGiaI.Enter
        Try
            'Kiểm Tra Tồn Tại Của Sản Phẩm Được Chọn

        Catch ex As Exception

        End Try
    End Sub
End Class